package jdbc.db;


import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;

public class MySQLBatch {

    //数据库连接对象
    private static Connection connection;
    private static AtomicInteger count = new AtomicInteger();

    private final static int maxBatchSize = 50000;


    public static void main(String[] args) {
        File file = new File("/Users/xiao/Desktop/yfhunt/data_ex/");
        File[] files = file.listFiles((f, n) -> !(f.isFile() && n.contains(".sql")));
        List<String> list = new ArrayList<>();
        if (files != null) {
            for (File f : files) {
                list.add(f.getAbsolutePath());
            }
        }
        MySQLBatch mySQLHandle = new MySQLBatch();
        ExecutorService executors = Executors.newFixedThreadPool(6);
        for (String path : list) {
            executors.execute(() -> {
                System.out.println(Thread.currentThread().getName() + ":" + path);
                String table = path.substring(path.lastIndexOf("/") + 1, path.lastIndexOf("."));
                mySQLHandle.runSqlByReadFileContentEx(path, table);
                System.out.println("第" + count.decrementAndGet() + "表" + table + "完成");
            });
        }
    }

    //带参数构造函数，用于指定参数连接数据库
    public MySQLBatch() {
        try {
            //获取数据库连接
            connection = DBUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            connection = null;
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public static void ReleaseConnect() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * @param sql 包含待执行的SQL语句的ArrayList集合
     * @return int 影响的函数
     * @方法描述：批量执行SQL语句
     */
    public int batchDate(ArrayList<String> sql, String table) {
        try {
            Statement st = connection.createStatement();
            connection.setAutoCommit(true);
            System.out.println(Thread.currentThread().getName() + " 清除数据 " + table);
            try {
                int capacity = 0, count = 0, maxCapacity = maxBatchSize;
                if (sql.size() <= maxCapacity) {//小于等于max
                    for (String subsql : sql) {
                        st.addBatch(subsql);
                    }
                    st.executeBatch();
                } else {//大余
                    int time = 0;
                    for (int i = 0; i < sql.size(); i++) {
                        capacity++;
                        count++;
                        String subsql = sql.get(i);
                        st.addBatch(subsql);
                        if (capacity == maxCapacity) {
                            st.executeBatch();
                            st.clearBatch();
                            capacity = 0;
                            time++;
                            System.out.println(Thread.currentThread().getName() + " " + table + ":" + time + "批处理量：" + maxCapacity + ",count=" + count);
                        } else if (count == sql.size()) {
                            st.executeBatch();
                            capacity = 0;
                            System.out.println(Thread.currentThread().getName() + " " + table + "批处理结束" + sql.size());
                        }
                    }
                }
            } catch (Exception e) {
                System.out.println(Thread.currentThread().getName() + " " + table + "语句执行错误List,稍后逐行执行：" + e.getMessage());
                for (String subsql : sql) {
                    try {
                        st.execute(subsql);
                    } catch (Exception e2) {
                        System.out.println(Thread.currentThread().getName() + " " + table + "语句执行错误：" + e2.getMessage());
                    }
                }
            }
            return 1;
        } catch (Exception e) {
            return 0;
        }
    }


    public void cleanTable(String table){
        try {
            Statement st = connection.createStatement();
            connection.setAutoCommit(true);
            st.executeUpdate("TRUNCATE TABLE " + table);
            connection.commit();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 以行为单位读取文件，并将文件的每一行格式化到ArrayList中，常用于读面向行的格式化文件
     */
    private ArrayList<String> readFileByLines(String filePath) throws Exception {
        ArrayList<String> listStr = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new InputStreamReader(
                    new FileInputStream(filePath), "UTF-8"));
            String tempString = null;
            int flag = 0;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
                // 显示行号
                // System.out.println("line " + line + ": " + tempString);
                if (tempString.trim().equals(""))
                    continue;
                if (tempString.substring(tempString.length() - 1).equals(";")) {
                    if (flag == 1) {
                        sb.append(tempString);
                        listStr.add(sb.toString());
                        sb.delete(0, sb.length());
                        flag = 0;
                    } else
                        listStr.add(tempString);
                } else {
                    flag = 1;
                    sb.append(tempString);
                }

            }

            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }

        return listStr;
    }

    /**
     * 读取文件内容到SQL中执行
     *
     * @param sqlPath SQL文件的路径：如：D:/TestProject/web/sql/脚本.Sql
     * @param table
     */
    public void runSqlByReadFileContent(String sqlPath, String table) {
        try {
            cleanTable(table);
            ArrayList<String> sqlStr = readFileByLines(sqlPath);
            if (sqlStr.size() > 0) {
                int num = batchDate(sqlStr, table);
                if (num > 0)
                    System.out.println("执行成功");
                else
                    System.out.println("未有执行的SQL语句");
            } else {
                System.out.println("没有需要执行的SQL语句");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 以行为单位读取文件，并将文件的每一行格式化到ArrayList中，常用于读面向行的格式化文件
     */
    private ArrayList<String> readFileByLinesEx(String filePath, String table) throws Exception {
        ArrayList<String> listStr = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new InputStreamReader(
                    new FileInputStream(filePath), "UTF-8"));
            String tempString = null;
            int flag = 0;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
                // 显示行号
                // System.out.println("line " + line + ": " + tempString);
                if (tempString.trim().equals(""))
                    continue;
                if (tempString.substring(tempString.length() - 1).equals(";")) {
                    if (flag == 1) {
                        sb.append(tempString);
                        listStr.add(sb.toString());
                        sb.delete(0, sb.length());
                        flag = 0;
                    } else
                        listStr.add(tempString);
                } else {
                    flag = 1;
                    sb.append(tempString);
                }
                if (listStr.size() >= maxBatchSize * 5) {
                    int num = batchDate(listStr, table);
                    if (num > 0)
                        System.out.println("执行成功");
                    else
                        System.out.println("未有执行的SQL语句");
                    listStr.clear();
                }
            }
            if (listStr.size() > 0 && listStr.size() <= maxBatchSize * 5) {
                int num = batchDate(listStr, table);
                if (num > 0)
                    System.out.println("执行成功");
                else
                    System.out.println("未有执行的SQL语句");
            } else {
                System.out.println("没有需要执行的SQL语句");
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }

        return listStr;
    }

    public void runSqlByReadFileContentEx(String sqlPath, String table) {
        try {
            cleanTable(table);
            readFileByLinesEx(sqlPath, table);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
